﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind.NET.Model;
using Northwind.NET.Settings;

namespace Northwind.NET.BLL
{
    [System.ComponentModel.DataObject()]
    public class Reports
    {
        public static dynamic SalesAnalysisReport()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            var OrderSubtotals = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                     OrderID = g.Key,
                                     Subtotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
                                 };
            var reportDataSet = from d in OrderSubtotals
                                join o in _context.Order on d.OrderID equals o.ID
                                join e in _context.Employee on o.EmployeeID equals e.ID
                                where (o.ShippedDate != null)
                                orderby e.LastName ascending, e.FirstName ascending,
                                        o.ShippedDate ascending, o.ID ascending
                                let monthNum = ((DateTime)o.ShippedDate).Month
                                select new
                                {
                                    e.Country,
                                    e.LastName,
                                    e.FirstName,
                                    ShippedYearNum = ((DateTime)o.ShippedDate).Year,
                                    ShippedQuarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                 (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                 (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                 (monthNum >= 10 && monthNum <= 12) ? 4 : -1,
                                    ShippedMonthNum = monthNum,
                                    o.ShippedDate,
                                    OrderID = o.ID,
                                    SaleAmount = d.Subtotal
                                };


            return reportDataSet; 

        } 

        public static dynamic EmployeeSalesByCountryReport(DateTime? dateFrom, DateTime? dateTo)
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            var orderTotals = from d in _context.OrderDetail
                              group d by d.OrderID into g
                              select new
                              {
                                  OrderID = g.Key,
                                  OrderTotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
                              };
            var reportDataSet = from o in _context.Order
                                join d in orderTotals on o.ID equals d.OrderID
                                join e in _context.Employee on o.EmployeeID equals e.ID
                                where o.ShippedDate != null &&
                                      (dateFrom == null && dateTo == null) ||
                                      (o.ShippedDate >= dateFrom && o.ShippedDate <= dateTo)
                                select new
                                {
                                    e.Country,
                                    e.LastName,
                                    e.FirstName,
                                    o.ShippedDate,
                                    OrderID = o.ID,
                                    d.OrderTotal
                                };
            
            return reportDataSet;
        } 


        public static dynamic SalesByYearReport(int? yearNumArg)
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            var udf_OrderSubtotals = from d in _context.OrderDetail
                                     group d by d.OrderID into g
                                     select new
                                     {
                                         OrderID = g.Key,
                                         Subtotal = g.Sum(d => (float?)d.UnitPrice * d.Quantity * (1 - d.Discount))
                                     };

            var reportDataSet = from d in udf_OrderSubtotals
                                join o in _context.Order on d.OrderID equals o.ID
                                let yearNum = ((DateTime)o.ShippedDate).Year
                                let monthNum = ((DateTime)o.ShippedDate).Month
                                let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                 (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                 (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                 (monthNum >= 10 && monthNum <= 12) ? 4 : -1
                                where (yearNumArg == null || yearNum == yearNumArg) &&
                                       o.ShippedDate != null
                                select new
                                {
                                    o.ShippedDate,
                                    d.OrderID,
                                    d.Subtotal,
                                    QuarterNum = quarterNum,
                                    Year = yearNum
                                };

        //  <DataField>ShippedDate</DataField>
        //  <rd:TypeName>System.DateTime</rd:TypeName>
        //</Field>
        //<Field Name="OrderID">
        //  <DataField>OrderID</DataField>
        //  <rd:TypeName>System.Int32</rd:TypeName>
        //</Field>
        //<Field Name="Subtotal">
        //  <DataField>Subtotal</DataField>
        //  <rd:TypeName>System.Decimal</rd:TypeName>
        //</Field>
        //<Field Name="Year">
        //  <DataField>Year</DataField>
        //  <rd:TypeName>System.Int32</rd:TypeName>
        //</Field>
        //<Field Name="QuarterNum">
        //  <DataField>QuarterNum</DataField>
        //  <rd:TypeName>System.Int32</rd:TypeName>


            return reportDataSet; 
        } 


        public static dynamic SalesTotalsByAmountReport(int? yearNumArg)
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

           var reportDataSet1 = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                     OrderId = g.Key,
                                     OrderTotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
                                 };
            var reportDataSet2 = from d in reportDataSet1
                                 join o in _context.Order on d.OrderId equals o.ID
                                 join c in _context.Customer on o.CustomerId equals c.ID
                                 let yearNum = (o.OrderDate != null) ? ((DateTime)o.OrderDate).Year : -1
                                 where yearNumArg == null || yearNum == yearNumArg
                                 orderby d.OrderTotal descending
                                 select new
                                 {
                                     SaleAmount = d.OrderTotal,
                                     o.ID,
                                     c.Name,
                                     o.ShippedDate,
                                     GroupValue = (int)d.OrderTotal / 1000,
                                     OrderYear = yearNum
                                 };

            return reportDataSet2;



        }        


        public static dynamic  SalesTotalsByAmountReport1()
        {
            //SimpleReportsDataSet_SalesTotalsByAmountReport

            //SELECT [OrderSubTotals].Subtotal AS SaleAmount, 
            //[Order].ID, Customer.Name, [Order].ShippedDate, 
            //CLNG(Fix([OrderSubTotals].Subtotal/1000)) AS GroupValue
            //FROM Customer, [Order], [SELECT [OrderDetail].OrderID, 
            //Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal 
            //FROM OrderDetail GROUP BY [OrderDetail].OrderID]. AS OrderSubTotals
            //WHERE Customer.ID=Order.CustomerID And Order.ID=OrderSubTotals.OrderId 
            //And (OrderSubtotals.Subtotal>2500) 
            //And (Order.ShippedDate Between #1/1/1997# And #12/31/1997#)
            //ORDER BY [OrderSubTotals].Subtotal DESC;

            NorthwindNETEntities _context = Globals.CreateObjectContext();

            int? yearNumArg = null;

            //SELECT     [Order].ID AS OrderID, Customer.Name AS CustomerName, [Order].OrderDate, YEAR([Order].OrderDate) AS OrderYear, OrderTotals.OrderTotal
            //FROM         Customer INNER JOIN
            //                      [Order] ON Customer.ID = [Order].CustomerId INNER JOIN
            // (SELECT     OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS OrderTotal
            //             FROM          OrderDetail
            //  GROUP BY OrderID) 
            // AS OrderTotals ON [Order].ID = OrderTotals.OrderID
            //WHERE     (YEAR([Order].OrderDate) = @OrderYear) AND (OrderTotals.OrderTotal &gt; @OrderTotal)

            var reportDataSet1 = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                     OrderId = g.Key,
                                     OrderTotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
                                 };
            var reportDataSet2 = from d in reportDataSet1
                                 join o in _context.Order on d.OrderId equals o.ID
                                 join c in _context.Customer on o.CustomerId equals c.ID
                                 let yearNum = (o.OrderDate != null) ? ((DateTime)o.OrderDate).Year : -1
                                 where yearNumArg == null || yearNum == yearNumArg
                                 orderby d.OrderTotal descending
                                 select new
                                 {
                                     OrderID = o.ID,
                                     CustomerName = c.Name,
                                     o.OrderDate,
                                     OrderYear = yearNum,
                                     d.OrderTotal
                                 };


            return reportDataSet2; 

        }        
 

        public class ProductCategoriesRow
        {
            public int? CategoryID { get; set; }
            public string CategoryName { get; set; } 
        }

        public static List<ProductCategoriesRow> ProductCategories()
        {
            List<string> list = new List<string>();

            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Category 
                                select new ProductCategoriesRow 
                                {
                                     CategoryID = c.ID, 
                                     CategoryName = c.Name 
                                };

            return reportDataSet.ToList<ProductCategoriesRow>();   
        }

        public static dynamic SalesByCategory(int? categoryId)
        {
            //SimpleReportsDataSet_SalesByCategory

            //SELECT [OrderDetail].OrderID, [OrderDetail].ProductID, Product.Name AS ProductName, [OrderDetail].UnitPrice,
            //[OrderDetail].Quantity, [OrderDetail].Discount, CCur(([OrderDetail].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ExtendedPrice
            //FROM Product, OrderDetail
            //WHERE Product.ID=OrderDetail.ProductID;


            //PARAMETERS categoryId Long;
            //SELECT Product.CategoryID, Category.Name AS CategoryName, Product.Name AS ProductName, 
            //Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
            //FROM Category, Product, [Order], 
            //[SELECT * FROM udf_OrderDetailsExtended]. AS OrderDetailsExtended
            //WHERE Order.ID=OrderDetailsExtended.OrderID And Product.ID=OrderDetailsExtended.ProductID And
            // Category.ID=Product.CategoryID And (categoryId Is Null Or Category.ID=categoryId)
            //GROUP BY Product.CategoryID, Category.Name, Product.Name
            //ORDER BY Category.Name;

            //TODO: optimize

            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var udf_OrderDetailsExtended = from d in _context.OrderDetail
                                           join p in _context.Product
                                            on d.ProductID equals p.ID
                                           select new
                                           {
                                               d.OrderID,
                                               d.ProductID,
                                               ProductName = p.Name,
                                               UnitPrice = (float?)d.UnitPrice,
                                               d.Quantity,
                                               d.Discount,
                                               ExtendedPrice = (float?)d.UnitPrice * d.Quantity * (1 - d.Discount)
                                           };

            const string DELIMITER = "{***}";
            var reportDataSet1 = from r in udf_OrderDetailsExtended
                                 join o in _context.Order on r.OrderID equals o.ID
                                 join p in _context.Product on r.ProductID equals p.ID
                                 join c in _context.Category on p.CategoryID equals c.ID
                                 where categoryId == null || c.ID == categoryId 
                                 select new
                                 {
                                     GroupKey = c.Name + DELIMITER + p.Name,
                                     p.CategoryID,
                                     CategoryName = c.Name,
                                     ProductName = p.Name,
                                     r.ExtendedPrice
                                     //Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
                                 };

            var reportDataSet2 = from r in reportDataSet1
                                 group r by r.GroupKey into g
                                 select new
                                 {
                                     g.Key,
                                     ProductSales = g.Sum(r => r.ExtendedPrice)
                                 };

            var reportDataSet3 = from r in reportDataSet2
                                 let pos = r.Key.IndexOf(DELIMITER)
                                 let categoryName = r.Key.Substring(0, pos)
                                 let productName = r.Key.Substring(pos + DELIMITER.Length)
                                 join c in _context.Category on categoryName equals c.Name
                                 orderby c.Name
                                 select new
                                 {
                                     GroupKey = r.Key,
                                     CategoryId = c.ID,
                                     CategoryName = categoryName,
                                     ProductName = productName,
                                     r.ProductSales
                                 };
            return reportDataSet3;
        }

        public static dynamic SummaryOfSalesByQuarter()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

           var reportDataSet1 = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                     OrderId = g.Key,
                                     Subtotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
                                 };
            var reportDataSet2 = from o in _context.Order
                                 join d in reportDataSet1 on o.ID equals d.OrderId
                                 where o.ShippedDate != null
                                 select new
                                 {
                                     o.ShippedDate,
                                     OrderId = o.ID,
                                     d.Subtotal
                                 };

            var reportDataSet3 = from r in reportDataSet2
                                 let yearNum = ((DateTime)r.ShippedDate).Year
                                 let monthNum = ((DateTime)r.ShippedDate).Month
                                 let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                  (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                  (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                  (monthNum >= 10 && monthNum <= 12) ? 4 : -1
                                 let groupKey = quarterNum * 10000 + yearNum
                                 select new
                                 {
                                     GroupByKey = groupKey,
                                     YearNum = yearNum,
                                     MonthNum = monthNum,
                                     QuarterNum = quarterNum,
                                     r.OrderId,
                                     r.Subtotal
                                 };

            var reportDataSet4 = from r in reportDataSet3
                                 group r by r.GroupByKey into g
                                 select new
                                 {
                                     GroupKey = g.Key,
                                     OrdersCount = g.Count(r => r.OrderId != null),
                                     SalesAmount = g.Sum(r => r.Subtotal)
                                 };

            var reportDataSet5 = from r in reportDataSet4
                                 let yearNum = r.GroupKey - ((r.GroupKey / 10000) * 10000)
                                 let quarterNum = r.GroupKey / 10000
                                 orderby r.GroupKey
                                 select new
                                 {
                                     r.GroupKey,
                                     YearNum = yearNum,
                                     QuarterNum = quarterNum,
                                     r.OrdersCount,
                                     r.SalesAmount
                                 };            
            return reportDataSet5;
        }


        public static dynamic SummaryOfSalesByYear()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            var reportDataSet1 = from d in _context.OrderDetail
                                 group d by d.OrderID into g
                                 select new
                                 {
                                     OrderId = g.Key,
                                     Subtotal = g.Sum(r => (float?)r.UnitPrice * r.Quantity * (1 - r.Discount))
                                 };
            var reportDataSet2 = from o in _context.Order
                                 join d in reportDataSet1 on o.ID equals d.OrderId
                                 where o.ShippedDate != null
                                 select new
                                 {
                                     o.ShippedDate,
                                     OrderId = o.ID,
                                     d.Subtotal
                                 };

            var reportDataSet3 = from r in reportDataSet2
                                 let yearNum = ((DateTime)r.ShippedDate).Year
                                 let monthNum = ((DateTime)r.ShippedDate).Month
                                 let quarterNum = (monthNum >= 1 && monthNum <= 3) ? 1 :
                                                  (monthNum >= 4 && monthNum <= 6) ? 2 :
                                                  (monthNum >= 7 && monthNum <= 9) ? 3 :
                                                  (monthNum >= 10 && monthNum <= 12) ? 4 : -1
                                 let groupKey = yearNum * 10 + quarterNum
                                 select new
                                 {
                                     GroupByKey = groupKey,
                                     YearNum = yearNum,
                                     MonthNum = monthNum,
                                     QuarterNum = quarterNum,
                                     r.OrderId,
                                     r.Subtotal
                                 };

            var reportDataSet4 = from r in reportDataSet3
                                 group r by r.GroupByKey into g
                                 select new
                                 {
                                     GroupKey = g.Key,
                                     OrdersCount = g.Count(r => r.OrderId != null),
                                     SalesAmount = g.Sum(r => r.Subtotal)
                                 };

            var reportDataSet5 = from r in reportDataSet4
                                 let yearNum = r.GroupKey / 10
                                 let quarterNum = r.GroupKey - yearNum * 10
                                 orderby r.GroupKey
                                 select new
                                 {
                                     r.GroupKey,
                                     YearNum = yearNum,
                                     QuarterNum = quarterNum,
                                     r.OrdersCount,
                                     r.SalesAmount
                                 };
            return reportDataSet5; 
        }

        public static dynamic Invoice(int? orderId)
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from o in _context.Order
                                join d in _context.OrderDetail on o.ID equals d.OrderID
                                join p in _context.Product on d.ProductID equals p.ID
                                join c in _context.Customer on o.CustomerId equals c.ID
                                join e in _context.Employee on o.EmployeeID equals e.ID
                                join s in _context.Shipper on o.ShipperId equals s.ID
                                where (orderId == null || o.ID == orderId)
                                select new
                                {
                                    o.ID,
                                    o.CustomerId,
                                    o.ShipName,
                                    o.ShipAddress,
                                    o.ShipCity,
                                    o.ShipRegion,
                                    o.ShipPostalCode,
                                    o.ShipCountry,
                                    o.OrderDate,
                                    o.RequiredDate,
                                    o.ShippedDate,
                                    d.UnitPrice,
                                    d.Quantity,
                                    d.Discount,
                                    ExtendedPrice = (float?)d.UnitPrice * d.Quantity * (1 - d.Discount),
                                    o.Freight,
                                    d.ProductID,
                                    o.ShipperId,
                                    o.EmployeeID,
                                    d.OrderID,
                                    SalesPerson = e.FirstName + " " + e.LastName,
                                    c.ContactName,
                                    c.Address,
                                    c.City,
                                    c.Region,
                                    c.PostalCode,
                                    c.Country,
                                    ProductName = p.Name,
                                    ShipVia = s.Name
                                };
            return reportDataSet;
        }

        public static List<string> CustomerCountries()
        {
            List<string> list = new List<string>();

            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Customer
                                group c by c.Country into g
                                select new
                                {
                                    CountryName = g.Key
                                };

            foreach (var c in reportDataSet)
            {
                list.Add(c.CountryName);
            }

            return list;
        }

        public static dynamic CustomerLabels(string countryName)
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Customer
                                where string.IsNullOrEmpty(countryName) || 
                                      string.Compare(c.Country, countryName, true) == 0
                                select new
                                {
                                    c.ID,
                                    c.Code,
                                    c.Name,
                                    c.Address,
                                    c.City,
                                    c.Region,
                                    c.PostalCode,
                                    c.Country
                                };

            //List<string> list = new List<string>(); 
            //foreach (var r in reportDataSet)
            //{
            //    list.Add(r.Name);  
            //}
            return reportDataSet;
        }

        public static dynamic ProductByCategory()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from c in _context.Category
                                join p in _context.Product
                                on c.ID equals p.CategoryID
                                select new
                                {
                                    SortGroupLetter = p.Name.Substring(0, 1),
                                    ProductName = p.Name,
                                    CategoryName = c.Name,
                                    p.QuantityPerUnit,
                                    p.UnitsInStock
                                };
            return reportDataSet; 
        }

        public class ShippersReportRow
        {
            public int ShipperID { get; internal set; }
            public string ShipperName { get; internal set; }
            public string Phone { get; internal set; }

        }

        public static List<ShippersReportRow> Shippers()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var reportDataSet = from s in _context.Shipper
                                select new ShippersReportRow
                                {
                                    ShipperID = s.ID,
                                    ShipperName = s.Name,
                                    Phone = s.Phone //,
                                    //s.RowTimeStamp
                                };
            return reportDataSet.ToList <ShippersReportRow>(); 
        }

        public static dynamic Catalog()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //SELECT Category.ID AS CategoryId, Category.Name AS CategoryName, 
            // Category.Description, Category.Picture, Product.ID AS ProductId,
            // Product.Name AS ProductName, Product.QuantityPerUnit, Product.UnitPrice
            //FROM Category, Product
            //WHERE Category.ID = Product.CategoryId;
            var reportDataSet = from c in _context.Category
                                join p in _context.Product
                                on c.ID equals p.CategoryID
                                select new
                                {
                                    CategoryId = c.ID,
                                    CategoryName = c.Name,
                                    c.Description,
                                    c.Picture,
                                    ProductId = p.ID,
                                    ProductName = p.Name,
                                    p.QuantityPerUnit,
                                    p.UnitPrice
                                };
            return reportDataSet;
        }


        [System.ComponentModel.DataObject()]
        public class AlphabeticalListOfProductsReportRow
        {
            public string SortGroupLetter { get; internal set; }
            public string ProductName { get; internal set; }
            public string CategoryName { get; internal set; }
            public string QuantityPerUnit { get; internal set; }
            public short? UnitsInStock { get; internal set; }

            [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
            public static List<AlphabeticalListOfProductsReportRow> GetReportDataSet()
            {
                // old style
                return Reports.AlphabeticalListOfProducts().ToList<AlphabeticalListOfProductsReportRow>();
            }
        }
        
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static IQueryable<AlphabeticalListOfProductsReportRow> AlphabeticalListOfProducts()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //vw_AlphabeticalListOfProductsReport
            //SELECT Mid(p.Name,1,1) AS SortGroupLetter, p.Name AS ProductName, c.Name AS CategoryName, p.QuantityPerUnit, p.UnitsInStock
            //FROM Category AS c, Product AS p
            //WHERE c.ID=p.CategoryId
            var vw_AlphabeticalListOfProductsReport = 
                          from c in _context.Category
                          join p in _context.Product
                          on c.ID equals p.CategoryID
                          select new AlphabeticalListOfProductsReportRow
                          {
                              SortGroupLetter = p.Name.Substring(0, 1) + "+", // "+" added to test
                              ProductName = p.Name,
                              CategoryName = c.Name,
                              QuantityPerUnit = p.QuantityPerUnit,
                              UnitsInStock = p.UnitsInStock
                          };
            return vw_AlphabeticalListOfProductsReport;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static dynamic AlphabeticalListOfProducts_DynamicDataSet()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //vw_AlphabeticalListOfProductsReport
            //SELECT Mid(p.Name,1,1) AS SortGroupLetter, p.Name AS ProductName, c.Name AS CategoryName, p.QuantityPerUnit, p.UnitsInStock
            //FROM Category AS c, Product AS p
            //WHERE c.ID=p.CategoryId
            var vw_AlphabeticalListOfProductsReport =
                          from c in _context.Category
                          join p in _context.Product
                          on c.ID equals p.CategoryID
                          select new //AlphabeticalListOfProductsReportRow
                          {
                              SortGroupLetter = p.Name.Substring(0, 1) + "++", // "++" added to test
                              ProductName = p.Name,
                              CategoryName = c.Name,
                              QuantityPerUnit = p.QuantityPerUnit,
                              UnitsInStock = p.UnitsInStock
                          };
            return vw_AlphabeticalListOfProductsReport;
        }

    }
}
